package com.tsmti.activiti.dao.impl;

import com.tsmti.activiti.dao.ActExtendDao;
import com.tsmti.activiti.dto.ActUserDto;
import com.tsmti.activiti.dto.ProcessTaskDto;
import com.tsmti.activiti.entity.ExtendActModel;
import com.tsmti.activiti.utils.ActConstants;
import com.tsmti.core.common.dao.impl.BaseDaoImpl;
import com.tsmti.core.common.query.data.Queryable;
import com.tsmti.core.util.ConvertUtil;
import com.tsmti.core.util.DateUtils;
import com.tsmti.core.util.StringUtils;
import com.tsmti.system.entity.User;
import com.tsmti.util.UserUtils;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Package：com.tsmti.activiti.dao.impl
 * @Class：ActExtendDaoImpl
 * @Description： 对activiti的表写sql查询，不通过api查询
 * @Author：zcw
 * @Date：Created in 2018/8/3 11:09
 * @Company:
 * @Version：
 * @Modified By:
 */
@Repository("actExtendDao")
public class ActExtendDaoImpl extends BaseDaoImpl implements ActExtendDao{

    private final static String KEY_DEALID = "dealId";
    private final static String KEY_CODE = "code";
    private final static String KEY_BUSID = "busId";
    private final static String KEY_USERNAME = "userName";

    @Override
    public List<Map<String, Object>> queryFlowsByActKey(String actKey) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id_ defid,name_ name,deployment_Id_ deploymentId,description_ description from act_re_procdef ");
        sql.append(" where deployment_Id_ IN (SELECT deployment_Id_ FROM act_re_model WHERE key_ =:actKey");
        Query query=getSession().createSQLQuery(sql.toString()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        query.setString("actKey", actKey);
        return query.list();
    }

    @Override
    public List<ActUserDto> userWindowPage(Map<String, Object> params, int page, int rows) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT DISTINCT realname as userName,u.id as userId,");
        sql.append(" (SELECT name FROM pims_organization o WHERE o.id=u.id) AS orgName");
        sql.append(" FROM pims_user u,");
        sql.append(" (select usertype,nu.id as nodeUserId,ns.nodetype  FROM extend_act_nodeuser nu,extend_act_nodeset ns WHERE ns.nodeid=nu.nodeid AND nu.nodeid =:nodeId) nodeuser");
        sql.append(" WHERE ((usertype ='1' AND nodeUserId = u.id)");
        sql.append(" or (usertype = '2' AND u.id IN (SELECT userid FROM pims_user_role ur WHERE ur.roleid=nodeUserId))");
        sql.append(" or (usertype='3' AND u.orgid = nodeUserId))");
        if(!StringUtils.isEmpty(params.get(KEY_USERNAME))){
            sql.append(" AND realname LIKE :userName");
        }
        System.out.println(sql.toString());
        return listPageByAliasSql(ActUserDto.class,page,rows,sql.toString(),params);
    }

    @Override
    public List<ActUserDto> turnWindowList(Map<String, Object> params, int page, int rows) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select u.realname as userName,u.id as userId,o.name as orgName");
        sql.append(" from pims_user u,pims_user_organization r,pims_organization o");
        sql.append(" where u.id = r.userid and r.organizationid = o.id");
        if(!StringUtils.isEmpty(params.get(KEY_USERNAME))){
            sql.append(" AND realname LIKE :userName");
        }
        return listPageByAliasSql(ActUserDto.class,page,rows,sql.toString(),params);
    }

    @Override
    public Map<String, Object> queryBusiByBusId(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from ").append(params.get(ActConstants.ACT_BUSINESS_TABLENAME));
        sql.append(" where ").append(params.get(ActConstants.ACT_BUSINESS_PRIMARYKEY));
        sql.append("=?");
        Query query=getSession().createSQLQuery(sql.toString());
        query.setString(0, StringUtils.null2String(params.get(ActConstants.ACT_BUSINESS_PRIMARYKEYVALUE)));
        return (Map)query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }

    /**
     * 更新业务表状态及相关信息
     * @param params 更新内容
     * @return
     */
    @Override
    public int updateBusInfo(Map<String,Object> params) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(params.get(ActConstants.ACT_BUSINESS_TABLENAME)).append(" set ");
        sql.append("startTime=to_date('"+ DateUtils.getDateTime()+"','yyyy-MM-dd hh24:Mi:ss')");
        if(!StringUtils.isEmpty(params.get(ActConstants.ACT_BUSINESS_STARTUSER))){
            sql.append(",startUserId='" + params.get(ActConstants.ACT_BUSINESS_STARTUSER) +"'" );
        }
        if(!StringUtils.isEmpty(params.get(ActConstants.ACT_BUSINESS_INSTANCEID))){
            sql.append(",instanceId='" +params.get(ActConstants.ACT_BUSINESS_INSTANCEID) +"'" );
        }
        if(!StringUtils.isEmpty(params.get(ActConstants.ACT_BUSINESS_CODE))){
            sql.append(",code='" +params.get(ActConstants.ACT_BUSINESS_CODE)+"'");
        }
        if(!StringUtils.isEmpty(params.get(ActConstants.ACT_BUSINESS_ACTSTATUS))){
            sql.append(",actStatus='" +params.get(ActConstants.ACT_BUSINESS_ACTSTATUS)+"'");
        }
        if(!StringUtils.isEmpty(params.get(ActConstants.ACT_BUSINESS_ACTRESULT))){
            sql.append(",actResult='" +params.get(ActConstants.ACT_BUSINESS_ACTRESULT) +"'" );
        }

        sql.append(" where ").append(params.get(ActConstants.ACT_BUSINESS_PRIMARYKEY)).append("= '" +params.get(ActConstants.ACT_BUSINESS_PRIMARYKEYVALUE)+"'");
        System.out.println(sql.toString());
        return updateBySql(sql.toString());
    }

    /**
     * 待办列表条数
     * @param params    查询条件
     * @return
     */
    @Override
    public long findMyUpcomingPageCount(Map<String, Object> params) {
        return countByAliasSql(getMyUpcomingSql(params,true),params);
    }

    /**
     * 我的待办列表
     * @param params    查询条件
     * @param page      页码
     * @param rows      页数
     * @return
     */
    @Override
    public List<ProcessTaskDto> findMyUpcomingPage(Map<String, Object> params, int page, int rows) {
        return listPageByAliasSql(ProcessTaskDto.class,page,rows,getMyUpcomingSql(params,false),params);
    }

    /**
     * 已办列表条数
     * @param params    查询条件
     * @return
     */
    @Override
    public long findMyDonePageCount(Map<String, Object> params) {
        return countByAliasSql(getMyDoneSql(params,true),params);
    }

    /**
     * 我的已办列表
     * @param params    查询条件
     * @param page      页码
     * @param rows      页数
     * @return
     */
    @Override
    public List<ProcessTaskDto> findMyDoneList(Map<String, Object> params, int page, int rows) {
        return listPageByAliasSql(ProcessTaskDto.class,page,rows,getMyDoneSql(params,false),params);
    }

     @Override
    public int updateChangeBusInfo(Map<String, Object> params) {
        return 0;
    }

    @Override
    public long countActUser(Map<String, Object> params, Queryable queryable) {
        String sql = getUserListSql(params,queryable,true);
        return countBySql(sql,UserUtils.getOrgId());
    }

    /**
     * 获取审批人列表
     * @param params
     * @param queryable
     * @return
     */
    @Override
    public List<ActUserDto> findActUser(Map<String, Object> params, Queryable queryable) {
        String sql = getUserListSql(params,queryable,false);
        String userType =  ConvertUtil.null2String(params.get("userType"));
        String orgId = UserUtils.getOrgId();
        String userName = ConvertUtil.null2String(params.get("userName"));
        List<String> conditon = new ArrayList<>();
        conditon.add(orgId);
        if(!StringUtils.isEmpty(userName)){
            conditon.add(userName);
        }
        return listPageBySql(ActUserDto.class,queryable.getPageable().getPageNumber(),queryable.getPageable().getPageSize(),sql,conditon.toArray());
    }

    @Override
    public ExtendActModel getModelAndBusInfo(String modelId) {
        StringBuilder sql = new StringBuilder();
        sql.append("select m.*,b.actKey AS actKey");
        sql.append(" from extend_act_model m,extend_act_business b");
        sql.append(" where m.modelid = ? AND m.extendActBusinessid = b.id");
        sql.append(" and (m.delflag =0 or m.delflag is null)");
        List<ExtendActModel> actModelList = listBySql(ExtendActModel.class,sql.toString(),modelId);
        if(actModelList != null && actModelList.size() >0){
            return actModelList.get(0);
        }
        return null;
    }

    @Override
    public ExtendActModel getModelAndBusInfoByWorkflowId(String workflowId) {
        StringBuilder sql = new StringBuilder();
        sql.append("select m.*,b.actKey AS actKey");
        sql.append(" from extend_act_model m,extend_act_business b");
        sql.append(" where m.workflowId = ? AND m.extendActBusinessid = b.id");
        sql.append(" and (m.delflag =0 or m.delflag is null)");
        List<ExtendActModel> actModelList = listBySql(ExtendActModel.class,sql.toString(),workflowId);
        if(actModelList != null && actModelList.size() >0){
            return actModelList.get(0);
        }
        return null;
    }

    private String getUserListSql(Map<String, Object> params, Queryable queryable,boolean count){
        StringBuilder sql = new StringBuilder();
        String userType =  ConvertUtil.null2String(params.get("userType"));
        String userName = ConvertUtil.null2String(params.get("userName"));
        if(count){
            sql.append("select count(*) ");
        }
        if(ActConstants.ExamineType.USER.getValue().equals(userType)){
            //用户
            if(!count){
                sql.append(" select '1' as userType,u.realname as userName,u.userName as userNo,o.name as orgName");
            }
            sql.append(" from pims_user u,pims_user_organization r,pims_organization o");
            sql.append(" where u.id = r.userid and r.organizationid = o.id");
            sql.append(" and o.id = ? and (u.delFlag = 0 or u.delFlag is null)");
            if(!StringUtils.isEmpty(userName)){
                sql.append(" and u.realname like '%?%'");
            }
        }else if(ActConstants.ExamineType.DOCTOR.getValue().equals(userType)){
            //医生
            if(!count){
                sql.append(" select '4' as userType,u.name as userName,u.userNo,o.name as orgName");
            }
            sql.append(" from pims_doctorinformation u,pims_organization o");
            sql.append(" where u.orgId = o.id and o.id=? and (u.delFlag = 0 or u.delFlag is null)");
            if(!StringUtils.isEmpty(userName)){
                sql.append(" and u.name like '%?%'");
            }
        }else if(ActConstants.ExamineType.ROLE.getValue().equals(userType)){
            //角色
            if(!count){
                sql.append(" select '2' as userType,r.name as userName,r.code as userNo,o.name as orgName");
            }
            sql.append(" from pims_role r,pims_organization o");
            sql.append(" where r.orgId = o.id and o.id=? and (r.delFlag = 0 or r.delFlag is null)");
            if(!StringUtils.isEmpty(userName)){
                sql.append(" and r.name like '%?%'");
            }
        }else if(ActConstants.ExamineType.ORGAN.getValue().equals(userType)){
            //机构
            if(!count){
                sql.append(" select '3' as userType,name as userName,code as userNo,name as orgName");
            }
            sql.append(" from pims_organization ");
            sql.append(" where id=? and (delFlag = 0 or delFlag is null)");
            if(!StringUtils.isEmpty(userName)){
                sql.append(" and name like '%?%'");
            }
        }
        return sql.toString();
    }
    /**
     * 获取待办列表SQL
     * @param params    查询条件
     * @param count     是否计数查询 true:是 false:否
     * @return
     */
    private String getMyUpcomingSql(Map<String, Object> params,boolean count){
        StringBuilder sql = new StringBuilder();
        if(count){
            sql.append("select count(*) ");
        }else {
            sql.append("select  a.name_ AS taskName,a.CREATE_TIME_ AS createTime, a.id_ AS taskId,  a.proc_inst_id_ AS instanceId,fb.defid AS defId,");
            sql.append("fb.code,fb.actkey AS actKey,fb.busid AS busId,");
            sql.append("(SELECT name FROM extend_act_business ab WHERE fb.actkey=ab.actkey ) AS busName,");
            sql.append("(SELECT name FROM pims_doctorinformation u WHERE u.code =fb.startUserId)AS startUserName,");
            sql.append("(SELECT realName FROM pims_user u WHERE u.userName =a.ASSIGNEE_ ) AS dealName,");
            sql.append("(SELECT nodeType FROM extend_act_nodeset ns WHERE ns.defid=fb.defid and a.TASK_DEF_KEY_ = ns.NODEID) AS nodeType");
        }
        sql.append(" FROM extend_act_flowbus fb,act_ru_task a");
        sql.append(" WHERE a.proc_inst_id_= fb.instanceid");
        if(params !=null && params.size() >0){
            if(!StringUtils.isEmpty(params.get(KEY_DEALID))) {
                sql.append(" AND ASSIGNEE_ =:dealId");
            }
            if(!StringUtils.isEmpty(params.get(KEY_CODE))) {
                sql.append("  AND fb.code LIKE :code");
            }
            if(!StringUtils.isEmpty(params.get(KEY_BUSID))) {
                sql.append(" AND fb.busid = :busId");
            }
        }
        sql.append(" ORDER BY a.CREATE_TIME_ DESC");
        System.out.println(sql.toString());
        return sql.toString();
    }

    /**
     * 获取已办列表SQL
     * @param params    查询条件
     * @param count     是否计数查询 true:是 false:否
     * @return
     */
    private String getMyDoneSql(Map<String, Object> params,boolean count){
        StringBuilder sql = new StringBuilder();
        if(count){
            sql.append("select count(*) ");
        }else {
            sql.append("select  a.createtime,a.deal_time,a.bus_id AS busId,a.def_id AS defId,a.instance_id AS instanceId,a.task_id AS taskId,");
            sql.append("a.task_name AS taskName,a.app_opinion AS remark,fb.code,fb.status AS status,fb.act_key,");
            sql.append("(SELECT name FROM extend_act_business ab WHERE fb.act_key=ab.actkey ) AS busName,");
            sql.append("(SELECT user_name FROM pims_user u WHERE u.id =fb.startuserId)AS startUserName,");
            sql.append("(SELECT user_name FROM pims_user u WHERE u.id =a.dealid ) AS dealName,");
            sql.append("(SELECT user_name FROM pims_user u WHERE u.id =a.advanceid ) AS advanceName,");
            sql.append("(SELECT node_type FROM extend_act_nodeset ns WHERE ns.defid=fb.defid) AS nodeType");
        }
        sql.append(" FROM extend_act_flowbus fb,extend_act_tasklog a");
        sql.append(" WHERE a.instanceid= fb.instanceid");
        if(params !=null && params.size() >0){
            if(!StringUtils.isEmpty(params.get(KEY_DEALID))) {
                sql.append(" AND ASSIGNEE_ =:dealId");
            }
            if(!StringUtils.isEmpty(params.get(KEY_CODE))) {
                sql.append("  AND fb.code LIKE :code");
            }
            if(!StringUtils.isEmpty(params.get(KEY_BUSID))) {
                sql.append(" AND fb.bus_id = :busId");
            }
        }
        sql.append(" ORDER BY a.deal_time DESC");
        return sql.toString();
    }
}
